Creating New Columns And New Measures 8
1/Go to Edit Query/Transform
2/Duplicate Energy column
3/Rename the Duplicated column to exactly Legend Of Energy Type
4/Replace Values of the power plant name with 1 and 2.
1 means Renewable
2 means Nonrenewable
Replace
Natural Gas = 2
Coal= 2
Nuclear=2
Hydroelectric Conventional=1
Wind = 1
Solar Thermal and Photovoltaic=1
Geothermal = 1
*Do the same steps for all power plants.
5/ Change type to Text
Same Process As New Column#1
1/ Duplicate Energy column
2/Rename the Duplicated column to exactly Types of Energy 2
3/Replace
Natural Gas = 3
Coal= 2
Nuclear=4
Hydroelectric Conventional=1
Wind = 1
Solar Thermal and Photovoltaic=1
Geothermal = 1
4/Change Type to Whole Number
In this column we will create a date calendar
Merge 3 column
1/Date
2/Month Name
3/Year
After
2/Rename the column to Full Date
3/Change type to Date/Time
4/ Finally Click "Close and apply"
MAKE YEAR COLUMN
Filter out all negative value from the generation column.
Mini value must be 0
Run All this DAX below
1/
2/Run all the DAX one by one.
Dax Formulas
1/.0YOYRenewableKPI = IFERROR( ([.0Renewable total Cy]-[.0LyRenewable])/[.0LyRenewable],BLANK())
2/.0Renewable total Cy = Var Mdd=Max(Main_PowerSource[Year]) return CALCULATE([.Renewable TotalPower], Main_PowerSource[Year]=Mdd)
3/.0LyRenewable = (CALCULATE([.Renewable TotalPower], PARALLELPERIOD(Main_PowerSource[Full Date],-1,YEAR))/[.0Yearscount])
4/.0Yearscount = If (DISTINCTCOUNT(Main_PowerSource[Year])-1=0,1,DISTINCTCOUNT(Main_PowerSource[Year])-1)
5/.Renewable TotalPower = CALCULATE(sum(Main_PowerSource[Generation]),Main_PowerSource[Types of Energy 2]=1)
6/.0unichar=SWITCH(true(),[.0YOYRenewableKPI]>=[.02LyYOYRenewableKPI],UNICHAR(9650),[.0YOYRene wableKPI]<[.02LyYOYRenewableKPI],UNICHAR(9660))
7/.02LyYOYRenewableKPI=IFERROR(CALCULATE([.0YOYRenewableKPI],PARALLELPERIOD(Main_PowerSource[Full Date],-1,YEAR)),BLANK())
8/.YOY Renewable M = If ([.0YOYRenewableKPI]>=0,[.0YOYRenewableKPI],(([.2YOY Nonrenewable]*-1)+[.0YOYRenewableKPI]))
9/.2YOY Nonrenewable = IFERROR( ([.2Nonrenewable total Cy]-[.2LyNonRenewable])/[.2LyNonRenewable],BLANK())
10/.NonRenewable TotalPower=CALCULATE(sum(Main_PowerSource[Generation]),Main_PowerSource[Types of Energy 2]>1)
11/.2Nonrenewable total Cy = Var Mdd=Max(Main_PowerSource[Year]) return CALCULATE([.NonRenewable TotalPower], Main_PowerSource[Year]=Mdd)
12/..Generation% = DIVIDE([.1Total Generation Cy],CALCULATE([.1Total Generation Cy],ALLSELECTED(Main_PowerSource)))
13/.YOY Power Gen = IFERROR(([.1Total Generation Cy]-[.1Total Generation Ly])/[.1Total Generation Ly],blank())
14/.1Total Generation Ly = (CALCULATE(sum(Main_PowerSource[Generation]), PARALLELPERIOD(Main_PowerSource[Full Date],-1,YEAR))/[.0Yearscount])
15/.kpirenewablecolor = SWITCH(TRUE(),[.0YOYRenewableKPI] >=0,1,[.0YOYRenewableKPI]<0,2)
16/.Barcolor = Switch(true(),[.NonRenewable TotalPower]>=[.2LstNonrenewable],"#4ca973","#DE6A73")
17/.GBarChartColor = (SWITCH(True(),[.NonRenewable TotalPower]=sum(Main_PowerSource[Generation]),"#808080","#cd9f61"))
18/.0unichar=SWITCH(true(),[.0YOYRenewableKPI]>=[.02LyYOYRenewableKPI],UNICHAR(9650),[.0YOYRenewableKPI]<[.02LyYOYRenewableKPI],UNICHAR(9660))
19/.0unichorcolor = Switch(true(),[.0YOYRenewableKPI]>=0,"#22BC22","#ff0000")
20/.2LyNonRenewable = (CALCULATE([.NonRenewable TotalPower], PARALLELPERIOD(Main_PowerSource[Full Date],-1,YEAR))/[.0Yearscount])
21/.2LstNonrenewable = Var Yar=Min(Main_PowerSource[Year])-1 RETURN (CALCULATE([.NonRenewable TotalPower],Main_PowerSource[Year]=Yar))
22/.1Total Generation Cy = Var Mdd=Max(Main_PowerSource[Year]) return CALCULATE(sum(Main_PowerSource[Generation]), Main_PowerSource[Year]=Mdd)